import ExcelJS from 'exceljs'
import utils from './utils'

const {Workbook} = ExcelJS

// 必填项的标题颜色
const REQUIRED_COLOR = 'ffd31515'

/**
 * 列的枚举类型定义
 */
class ColumnEnum {
  /**
   *
   * @param {any} label
   * @param {any} [value] 当不指定值时，值与 label 相同
   */
  constructor(label, value) {
    this.label = label
    this.value = arguments.length === 1 ? label : value
  }
}

/**
 * 列定义
 */
class ColumnDef {
  /**
   *
   * @param {string} name 列的标题
   * @param {string} field 列的字段
   * @param {object} [options] 列选项
   * @param {boolean} [options.required=false] 列是否必填的。当其为 true 时，其值不能为空
   * @param {string} [options.type='string'] 列的类型，可选值见 ColumnDef.types
   * @param {ColumnEnum[]} [options.enums] 列的枚举定义
   * @param {CellStyle} [options.headerStyle=0] 此表的头部样式（仅导出数据时生效）
   * @param {function({value: any, column: ColumnDef})} [options.writeParser] 列的写处理函数
   * @param {function({value: any, column: ColumnDef})} [options.readParser] 列的读处理函数
   * @param {string | function(data: {value: any, column: ColumnDef}, io: string)} [options.invalidEnum] 发现无效枚举值时的处理方法；
   *  留空表示不允许无效值; 设置为 i 表示允许读取无效值; 设置为 o 表示允许写入无效值; 设置 io 允许读写无效值; 设置为函数表示自定义处理，函数返回处理后的值
   * @param {String} [options.help] 用于描述如何填写的帮助信息，此信息会汇集到帮助表中
   */
  constructor(name, field, options) {
    this.name = name
    this.field = field

    this.options = Object.assign(
      {
        required: false,
        type: ColumnDef.types.STRING,
        enums: [],
        headerStyle: {
          color: '',
          width: 0,
          italic: false,
          size: 12,
          bold: true
        },
        writeParser: null,
        readParser: null,
        invalidEnum: '',
        help: ''
      },
      options
    )
  }

  /**
   * 处理导入时的数据
   * @param value
   * @returns {string|number|*}
   */
  parseReadValue(value) {
    if (this.options.readParser) {
      value = this.options.readParser({
        value,
        column: this
      })
    }
    const isEmpty = value === undefined || value === null || value === ''
    if (this.options.required && isEmpty) {
      throw new Error('值不能为空')
    }
    if (!isEmpty) {
      let iVal, fVal
      switch (this.options.type) {
        case ColumnDef.types.DATE:
        case ColumnDef.types.DATETIME:
          value = this._parseReadDate(value)
          break
        case ColumnDef.types.NUMBER:
          iVal = parseInt(value)
          fVal = parseFloat(value)
          if (isNaN(iVal)) {
            throw new Error(`无效的数值 "${value}"`)
          }
          value = iVal === fVal ? iVal : fVal
          break
        default:
          break
      }
      if (this.options.enums.length && [ColumnDef.types.DATE, ColumnDef.types.DATETIME].indexOf(this.options.type) === -1) {
        value = this._parseReadEnum(value)
      }
    }
    return value
  }

  /**
   * 处理导出时的数据
   * @param value
   * @param {object} row
   * @returns {string|number|*}
   */
  parseWriteValue(value, row) {
    if (this.options.writeParser) {
      value = this.options.writeParser({
        value,
        column: this,
        row
      })
    }
    const isEmpty = value === undefined || value === null || value === ''
    if (!isEmpty) {
      switch (this.options.type) {
        case ColumnDef.types.DATE:
        case ColumnDef.types.DATETIME:
          value = this._parseWriteDate(value)
          break
        default:
          break
      }
      if (this.options.enums.length && [ColumnDef.types.DATE, ColumnDef.types.DATETIME].indexOf(this.options.type) === -1) {
        value = this._parseWriteEnum(value)
      }
    }
    return value
  }

  getEnumLabels() {
    return this.options.enums.map(e => e.label)
  }

  // /**
  //  *
  //  * @param {number} value
  //  * @returns {string}
  //  * @private
  //  */
  // _parseReadDate(value) {
  //   const d = value - 1
  //   const t = Math.round((d - Math.floor(d)) * 24 * 60 * 60)
  //   const date = new Date(1900, 0, d, 0, 0, t)
  //
  //   const dp = `${date.getFullYear()}-${this._pad(date.getMonth() + 1)}-${this._pad(date.getDate())}`
  //   const tp = `${this._pad(date.getHours())}:${this._pad(date.getMinutes())}:${this._pad(date.getSeconds())}`
  //
  //   return this.options.type === ColumnDef.types.DATETIME ? `${dp} ${tp}` : dp
  // }
  /**
   * 将读取到的日期 -8 小时
   * @param {Date|String} value
   * @returns {string}
   * @private
   */
  _parseReadDate(value) {
    if (typeof value === 'string') {
      const dValue = new Date(value)
      if (value.toString() === 'Invalid Date') {
        throw new Error(`无效的时间/日期值 "${value}"`)
      }
      value = dValue
    }
    if (Object.prototype.toString.call(value) !== '[object Date]') {
      throw new Error(`无效的时间/日期值 "${value}"`)
    }
    const timestamp = value.getTime()
    // 由于 GMT +0800，导致实际时间要多 8个小时
    const date = new Date(timestamp - 8 * 60 * 60 * 1000)

    const dp = `${date.getFullYear()}-${this._pad(date.getMonth() + 1)}-${this._pad(date.getDate())}`
    const tp = `${this._pad(date.getHours())}:${this._pad(date.getMinutes())}:${this._pad(date.getSeconds())}`

    return this.options.type === ColumnDef.types.DATETIME ? `${dp} ${tp}` : dp
  }

  _parseReadEnum(value) {
    for (const e of this.options.enums) {
      if (e.label === value) {
        return e.value
      }
    }
    const invalidEnum = this.options.invalidEnum
    if (invalidEnum) {
      if (typeof invalidEnum === 'string' && invalidEnum.indexOf('i') !== -1) {
        return value
      }
      if (typeof invalidEnum === 'function') {
        return invalidEnum({
          value,
          column: this
        }, 'i')
      }
    }
    throw new Error(`无效的枚举值 "${value}"`)
  }

  _parseWriteEnum(value) {
    for (const e of this.options.enums) {
      if (e.value === value) {
        return e.label
      }
    }
    const invalidEnum = this.options.invalidEnum
    if (invalidEnum) {
      if (typeof invalidEnum === 'string' && invalidEnum.indexOf('o') !== -1) {
        return value
      }
      if (typeof invalidEnum === 'function') {
        return invalidEnum({
          value,
          column: this
        }, 'o')
      }
    }
    throw new Error(`无效的枚举值 "${value}"`)
  }

  /**
   *
   * @param {string} value
   * @returns {string}
   * @private
   */
  _parseWriteDate(value) {
    return this.options.type === ColumnDef.types.DATETIME ? value : value.split(' ')[0]
  }

  _pad(num) {
    return num.toString().padStart(2, '0')
  }

  /**
   * 获取此表的表头注释
   * @returns {string}
   */
  getHeaderComment() {
    let helpText = this.options.help ? (this.options.help + '\n') : ''
    if (this.options.enums && this.options.enums.length) {
      helpText += `可选值:\n${this.options.enums.map(e => e.label).join('\r')}`
    }

    return helpText
  }

  getHelp() {
    const helpRow = [
      {
        richText: [{
          text: '列：'
        }, {
          font: {
            color: this.options.required ? {
              argb: REQUIRED_COLOR
            } : undefined
          },
          text: this.name
        }]
      },
      this.options.help
    ]
    if (this.options.enums && this.options.enums.length) {
      helpRow.push(`可选值: ${this.options.enums.map(e => e.label).join(',')}`)
    }
    return helpRow
  }
}

/**
 * 列数据的类型
 * @type {{DATE: string, NUMBER: string, STRING: string}}
 */
ColumnDef.types = {
  STRING: 'string',
  NUMBER: 'number',
  DATE: 'date',
  DATETIME: 'datetime'
}

/**
 * 表定义
 */
class SheetDef {
  /**
   *
   * @param {string} name Sheet名称
   * @param {ColumnDef[]} columns 列声明
   * @param {Object} [options] 选项
   * @param {function({data: {}, index: number, raw: {}}): boolean | {}} [options.rowHandler] 行的值处理器。返回 false 表示值无效
   * @param {number} [options.maxRowCount=0] 最多读取的数据行数
   * @param {String} [options.help] 用于描述如何填写的帮助信息，此信息会汇集到帮助表中
   */
  constructor(name, columns, options) {
    this.name = name
    this.columns = columns
    this.options = Object.assign(
      {
        maxRowCount: 0,
        help: '',
        rowHandler: null
      },
      options
    )
  }

  /**
   *
   * @param {Worksheet} sheet
   */
  read(sheet) {
    const data = []

    const {maxRowCount} = this.options

    // 存储列对应的索引
    // key: 字段名称，通过头列的 value 得到
    // value: 字段索引，通过头列的 col 得到
    const header = new Map()

    // 索引要从 1 开始
    sheet.eachRow(row => {
      if (maxRowCount && data.length === maxRowCount) {
        return false
      }
      // 先检查是否行的所有值都为空
      // 要是都为空，跳过此行
      if (!row.hasValues) {
        return
      }

      if (header.size === 0) {
        // 读取头
        row.eachCell(cell => {
          header.set(cell.text, cell.col)
        })
        return
      }

      const rowIndex = row.number

      const rowData = Object.create(null)

      this.columns.forEach((column) => {
        const name = column.name
        if (!header.has(name)) {
          throw new Error(`在表 "${this.name}" 中找不到列 "${name}"`)
        }
        try {
          const cell = row.getCell(header.get(name))
          let cellValue = cell.value
          // 当单元格的值为对象（如：超链接或邮箱）时，仅取其文本值
          if (Object.prototype.toString.call(cellValue) === '[object Object]') {
            if (cellValue.richText) {
              cellValue = cellValue.richText.map(item => item.text).join('')
            } else {
              cellValue = cellValue.text
            }
          }
          rowData[column.field] = column.parseReadValue(cellValue)
        } catch (e) {
          throw new Error(`表 "${this.name}" 第 ${rowIndex} 行 "${name}" ${e.message}`)
        }
      })

      if (this.options.rowHandler) {
        const result = this.options.rowHandler({
          data: rowData,
          raw: row,
          index: rowIndex - 1
        })
        if (result === false) {
          throw new Error(`表 "${this.name}" 第  ${rowIndex} 行值无效`)
        }
        if (result !== undefined) {
          const type = /^\[object ([^[]+)]$/.exec(Object.prototype.toString.call(result))[1]
          if (type !== 'Object') {
            throw new Error(`表 "${this.name} 的行处理函数返回值类型 "${type}" 无效：仅支持返回 object/false 类型`)
          }
        }
      }

      data.push(rowData)
    })

    return data
  }

  // noinspection JSValidateJSDoc
  /**
   * 将数据处理成可以写入 sheet 的数组
   * @param {(Object[] | any[][])} data
   * @param {boolean} withHelp
   * @return {Array}
   */
  write(data, withHelp) {
    const header = []
    this.columns.forEach(col => {
      const headerStyle = col.options.headerStyle
      const cellValue = {
        text: col.name,
        font: {
          color: {},
          size: headerStyle.size,
          italic: headerStyle.italic,
          bold: headerStyle.bold
        }
      }
      if (col.options.required) {
        cellValue.font.color.argb = REQUIRED_COLOR
      } else if (headerStyle.color) {
        cellValue.font.color.argb = headerStyle.color
      }
      header.push({
        richText: [cellValue]
      })
    })

    const rows = [header]

    data.forEach((row, rowIndex) => {
      const rowData = []
      this.columns.forEach((columnDef, colIndex) => {
        try {
          // 兼容行数据是 数组和对象
          const value = Array.isArray(row) ? row[colIndex] : row[columnDef.field]
          rowData.push(columnDef.parseWriteValue(value, row))
        } catch (e) {
          throw new Error(`表 "${this.name}" 的数据第 ${rowIndex} 行 "${name}" ${e.message}`)
        }
      })
      rows.push(rowData)
    })

    return rows
  }

  getHelp() {
    const columnDesc = []

    this.columns.forEach(col => {
      columnDesc.push(col.getHelp())
    })

    return [
      [{
        richText: [{
          text: `表: ${this.name}`,
          font: {
            bold: true
          }
        }]
      }, this.options.help],
      ...columnDesc,
      [],
      []
    ]
  }
}

/**
 * 帮助表选项
 */
class HelpSheetOption {
  /**
   *
   * @param {String} [help] 用于描述如何填写的帮助信息
   * @param {Object} [options] 选项
   * @param {String} [options.title=帮助] 帮助标题
   * @param {String} [options.sheetName=帮助] 帮助表名
   * @param {Number} [options.sheetIndex=0] 表位置，从左至右，对应从0开始；传入 -1 表示放在最后
   */
  constructor(help, options) {
    options = options || {}
    this.help = help
    this.sheetName = options.sheetName || '帮助'
    this.title = options.title || '帮助'
    this.sheetIndex = options.sheetIndex || 0
  }
}

/**
 * 工作薄定义
 */
class WorkbookDef {
  /**
   *
   * @param {SheetDef[]} sheets 此表格中要使用的表定义
   * @param {HelpSheetOption} [helpSheetOption] 帮助表选项
   */
  constructor(sheets, helpSheetOption) {
    this.sheets = sheets
    this.helpSheetOption = helpSheetOption || new HelpSheetOption()
  }

  /**
   * 读取文件内容
   * @param file
   * @returns {Promise<ArrayBuffer>}
   */
  async readFile(file) {
    const reader = new FileReader()
    const promise = new Promise((resolve, reject) => {
      reader.onload = function () {
        resolve(reader.result)
      }
      reader.onerror = function (e) {
        reader.abort()
        reject(e)
      }
    })
    reader.readAsArrayBuffer(file)

    return promise
  }

  /**
   * 从 文件对象 读取数据
   * @param {File} file
   */
  async read(file) {
    let dataBuffer = await this.readFile(file)

    const workbook = new ExcelJS.Workbook()
    await workbook.xlsx.load(dataBuffer)

    const result = []

    const sheetNamesFromFile = workbook.worksheets.map(sheet => sheet.name)

    this.sheets.forEach((sheetDef) => {
      const sheetName = sheetDef.name
      if (sheetNamesFromFile.indexOf(sheetName) === -1) {
        throw new Error(`找不到名称为 "${sheetName}" 的表`)
      }

      const sheet = workbook.getWorksheet(sheetName)

      result.push({
        name: sheetName,
        rows: sheetDef.read(sheet)
      })
    })

    return result
  }

  /**
   * 将数据写入文件的第一个 Sheet 中
   * 此时不能指定要写入的 Sheet (使用第一个 SheetDef)
   * @param {object[] | array[]} rows
   * @param {string} saveAs 另存为文件名(会自动添加 xlsx 扩展名)
   * @param {boolean} [withHelp=false] 是否包含帮助信息
   * @return {Promise<void>}
   */
  async writeFirst(rows, saveAs, withHelp) {
    const data = [{
      name: this.sheets[0].name,
      rows
    }]
    return this.write(data, saveAs, withHelp)
  }

  /**
   * 将数据写入文件
   * @param {[{name: string, rows: object[] | any[][]}]} data
   * @param {string} saveAs 另存为文件名(会自动添加 xlsx 扩展名)
   * @param {boolean} [withHelp=false] 是否包含帮助信息
   */
  async write(data, saveAs, withHelp) {
    const tempData = {}
    const {helpSheetOption} = this

    data.forEach(item => {
      if (!Array.isArray(item.rows)) {
        throw new Error(`Sheet(${item.name}) rows must be an array, got "${typeof item.rows}"`)
      }
      tempData[item.name] = item.rows
    })

    const helpInfo = withHelp ? [
      [{
        richText: [{
          text: helpSheetOption.title,
          font: {
            bold: true,
            size: 16
          }
        }]
      }],
      [helpSheetOption.help],
      [{
        richText: [{
          text: '表格标题行上的 '
        }, {
          font: {
            color: {
              argb: REQUIRED_COLOR
            },
            bold: true
          },
          text: '红色文字'
        }, {
          text: ' 表示此列必填'
        }]
      }],
      []
    ] : null

    const sheetData = []

    this.sheets.forEach((sheetDef) => {
      const sheetName = sheetDef.name
      sheetData.push({
        name: sheetName,
        rows: sheetDef.write(tempData[sheetName] || [], withHelp)
      })

      if (withHelp) {
        helpInfo.push(...sheetDef.getHelp())
      }
    })

    if (withHelp) {
      // 写帮助表
      const helpData = {
        name: helpSheetOption.sheetName,
        rows: helpInfo
      }
      if (helpSheetOption.sheetIndex === -1) {
        sheetData.push(helpData)
      } else {
        sheetData.splice(helpSheetOption.sheetIndex, 0, helpData)
      }
    }

    // 生成 工作薄
    // 为了能方便地
    const workbook = new Workbook()
    sheetData.forEach(({
      name,
      rows
    }) => {
      const sheet = workbook.addWorksheet(name)
      sheet.addRows(rows)
    })

    this.sheets.forEach(sheetDef => {
      let sheet = workbook.getWorksheet(sheetDef.name)
      sheetDef.columns.forEach((col, colIndex) => {
        // 设置表头宽度
        const sheetColumn = sheet.getColumn(colIndex + 1)
        const headerStyle = col.options.headerStyle
        if (headerStyle && headerStyle.width) {
          sheetColumn.width = headerStyle.width
        }
        // 每个表头
        // 添加注释
        if (withHelp) {
          // Keep that in mind: The index always start with 1.
          const headerRow = sheet.getRow(1)
          const cell = headerRow.getCell(colIndex + 1)
          // 以下部分，调整到了 SheetDef.write 中处理
          // // 必填项，将文字处理成红色
          // const color = col.options.required ? {
          //   argb: REQUIRED_COLOR
          // } : undefined
          // cell.value = {
          //   richText: [{
          //     font: {
          //       color,
          //       bold: true
          //     },
          //     text: cell.text
          //   }]
          // }

          const comment = col.getHeaderComment()
          if (comment) {
            cell.note = {
              texts: [{
                text: comment
              }],
              margins: {
                insetmode: 'custom',
                inset: [0.25, 0.25, 0.25, 0.25]
              }
            }
          }

          const enums = col.options.enums

          if (!enums || !enums.length) {
            return
          }
          // 给列添加枚举支持
          // 渲染下拉框
          const enumLabels = col.getEnumLabels().join(',')
          const validation = {
            type: 'list',
            allowBlank: !col.options.required,
            formulae: [`"${enumLabels}"`]
          }
          // 只能前 1000 行设置下拉
          for (let i = 2; i < 1000; i++) {
            const cell = sheet.getCell(i, colIndex + 1)
            cell.dataValidation = validation
          }
        }
      })
    })

    // 生成文件对象
    let buffer = await workbook.xlsx.writeBuffer()

    // 处理表头的注释大小不能适应内容的问题
    buffer = await utils.fixCommentStyle(buffer)

    const blobData = new Blob([buffer], {type: 'application/octet-stream'})

    if (!saveAs) {
      return blobData
    }
    utils.downloadData(blobData, saveAs + '.xlsx')
  }
}

export {WorkbookDef, SheetDef, ColumnDef, ColumnEnum}
